VERSION 1.0 CLASS
BEGIN
  MultiUse = -1  'True
  Persistable = 0  'NotPersistable
  DataBindingBehavior = 0  'vbNone
  DataSourceBehavior  = 0  'vbNone
  MTSTransactionMode  = 0  'NotAnMTSObject
END
Attribute VB_Name = "clsGetDirResData"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = True
Attribute VB_PredeclaredId = False
Attribute VB_Exposed = True
Option Explicit

Public Function GetDirectiveRespData(ByRef strFailReason As String, ByRef nNumResRecords As String, ByVal strServerName As String) As Boolean
Dim adoConn As ADODB.Connection
Dim objStrConn As Object
Dim strConnString As String
Dim RSCount As ADODB.Recordset
Dim RSDirResData As ADODB.Recordset
Dim RsProvTaxData As ADODB.Recordset
Dim adoComm1 As ADODB.Command
Dim adoComm2 As ADODB.Command
Dim adoComm3 As ADODB.Command
Dim iCounter As Integer

ServerName = strServerName

On Error GoTo PROC_ERROR

Set adoConn = New ADODB.Connection
With adoConn
    .ConnectionString = GetConnectionString() 'Johann (20090625): Centralised code for connection string added
    .Open
End With

Set RSDirResData = New ADODB.Recordset
With RSDirResData
    .CursorLocation = adUseClient
    .CursorType = adOpenKeyset
End With

'Get number of records returned
Set adoComm1 = New ADODB.Command
With adoComm1
    '.CommandText = "Select * from dir_response where req_seq_num = " & nReqSeqNum
    .CommandText = "Select count(*) RecordsReturned from dir_response where (REQUEST_STATUS = '07') and req_seq_num in (select req_seq_num from dir_interface_index where sars_response_yn = 'N')"
    .CommandType = adCmdText
    Set .ActiveConnection = adoConn
End With

'Get Response Data
Set adoComm2 = New ADODB.Command
With adoComm2
    '.CommandText = "Select * from dir_response where req_seq_num = " & nReqSeqNum
    .CommandText = "Select * from dir_response where (REQUEST_STATUS = '07') and req_seq_num in (select req_seq_num from dir_interface_index where sars_response_yn = 'N')"
    .CommandType = adCmdText
    Set .ActiveConnection = adoConn
End With
    
Set RSCount = adoComm1.Execute
Set RSDirResData = adoComm2.Execute

nNumResRecords = RSCount.Fields("RecordsReturned").Value

'Validate recordset exists
Dim dllRecordsetExist As clsIsRecordsetEmpty
Set dllRecordsetExist = New clsIsRecordsetEmpty

Dim blResponseRSEmpty As Boolean
Dim blProvTaxRSEmpty As Boolean

blResponseRSEmpty = dllRecordsetExist.IsEmpty(RSDirResData)


If blResponseRSEmpty = True Then
        GetDirectiveRespData = False
        strFailReason = "There were no approved responses to send to MQ"
        nNumResRecords = 0
        Exit Function
Else
    Do While Not RSDirResData.EOF

    If IsNull(RSDirResData.Fields("req_seq_num").Value) Then
        nReqSeqNum = ""
    Else
        nReqSeqNum = RSDirResData.Fields("req_seq_num").Value
    End If
    If IsNull(RSDirResData.Fields("request_status").Value) Then
        nReqStatus = ""
    Else
        nReqStatus = RSDirResData.Fields("request_status").Value
    End If
    If IsNull(RSDirResData.Fields("sars_appl_id").Value) Then
        nApplID = ""
    Else
        nApplID = RSDirResData.Fields("sars_appl_id").Value
    End If
    If IsNull(RSDirResData.Fields("income_tax_area_code").Value) Then
        nAreaCode = ""
    Else
        nAreaCode = RSDirResData.Fields("income_tax_area_code").Value
    End If
    If IsNull(RSDirResData.Fields("directive_number").Value) Then
        nDirNumber = ""
    Else
        nDirNumber = RSDirResData.Fields("directive_number").Value
    End If
    If IsNull(RSDirResData.Fields("issue_date").Value) Then
        dIssueDate = ""
    Else
        dIssueDate = RSDirResData.Fields("issue_date").Value
    End If
    If IsNull(RSDirResData.Fields("dir_start_date").Value) Then
        dStartDate = ""
    Else
        dStartDate = RSDirResData.Fields("dir_start_date").Value
    End If
    If IsNull(RSDirResData.Fields("dir_end_date").Value) Then
        dEndDate = ""
    Else
        dEndDate = RSDirResData.Fields("dir_end_date").Value
    End If
    If IsNull(RSDirResData.Fields("tax_free_portion").Value) Then
        nTaxFree = ""
    Else
        nTaxFree = RSDirResData.Fields("tax_free_portion").Value
    End If
    If IsNull(RSDirResData.Fields("gross_amount_paye").Value) Then
        nGrossPAYE = ""
    Else
        nGrossPAYE = RSDirResData.Fields("gross_amount_paye").Value
    End If
    If IsNull(RSDirResData.Fields("scod_dir_type").Value) Then
        sDirType = ""
    Else
        sDirType = RSDirResData.Fields("scod_dir_type").Value
    End If
    'Assessed Amount
    If IsNull(RSDirResData.Fields("assessed_tax_amt").Value) Then
        nAssAmt = 0
    Else
        nAssAmt = CDbl(RSDirResData.Fields("assessed_tax_amt").Value)
    End If
    'Provisional Tax Amount
    'Get Provisional Tax Amounts
    'New code added by Shane on 25/10/2002
    Set adoComm3 = New ADODB.Command
    With adoComm3
        .CommandText = "Select * from dir_provisional_tax where req_seq_num = " & nReqSeqNum
        .CommandType = adCmdText
        Set .ActiveConnection = adoConn
    End With
    Set RsProvTaxData = adoComm3.Execute
    blProvTaxRSEmpty = dllRecordsetExist.IsEmpty(RsProvTaxData)
    
    If blProvTaxRSEmpty = True Then
        nProvTaxTotalAmt = 0
    Else
        nProvTax = 0
        nProvTaxTotalAmt = 0
        Do While Not RsProvTaxData.EOF
            If RsProvTaxData.Fields("req_seq_num").Value = nReqSeqNum Then
                nProvTax = CDbl(RsProvTaxData.Fields("tax_amount").Value)
                nProvTaxTotalAmt = CDbl(nProvTaxTotalAmt) + CDbl(nProvTax)
        End If
        RsProvTaxData.MoveNext
        Loop
    End If
    'PAYE amount - employees tax
    If IsNull(RSDirResData.Fields("paye_amt").Value) Then
        nPAYEAmt = 0
    Else
        nPAYEAmt = CDbl(RSDirResData.Fields("paye_amt").Value)
    End If
    'Value added tax
    If IsNull(RSDirResData.Fields("vat_amount").Value) Then
        nVatAmt = 0
    Else
        nVatAmt = CDbl(RSDirResData.Fields("vat_amount").Value)
    End If
    'TOTAL TAX, PENALTY AND INTEREST
    nTotalAmt = CDbl(nAssAmt) + CDbl(nProvTaxTotalAmt) + CDbl(nPAYEAmt) + CDbl(nVatAmt)
    
    If IsNull(RSDirResData.Fields("it88_ref_no").Value) Then
        nIT88No = ""
    Else
        nIT88No = RSDirResData.Fields("it88_ref_no").Value
    End If
    
    Form1.Show
    
    'Write XML File
    Dim dllCreateXMLRes As clsCreateXMLRes
    Set dllCreateXMLRes = New clsCreateXMLRes
    
    'strXmlVersDtdHeader = dllCreateXMLRes.CreateXmlVersionDtdHeader()
    strXMLResponse = dllCreateXMLRes.XMLCreateResponseFile()

    'Update Index table / Update XML table
    Dim dllSendIntIndex As clsSendIntIndex
    Set dllSendIntIndex = New clsSendIntIndex
    
    If dllSendIntIndex.InsertIntoInterfaceIndex(lngNewInterfaceNo, nNewMessageID) Then
        If dllSendIntIndex.InsertIntoInterfaceXml(lngNewInterfaceNo, nNewMessageID, strXmlVersDtdHeader, strXMLResponse) Then
            If dllSendIntIndex.UpdateInterfaceIndex(nReqSeqNum) Then
                GetDirectiveRespData = True
            Else
                strFailReason = "InsertIntoInterfaceXml failed"
                GetDirectiveRespData = False
            End If
        Else
            strFailReason = "InsertIntoInterfaceXml failed"
            GetDirectiveRespData = False
        End If
    Else
        strFailReason = "InsertIntoInterfaceIndex failed"
        GetDirectiveRespData = False
    End If
                        

    If GetDirectiveRespData = True Then
        'Display Progress
        iCounter = iCounter + 1
        Form1.List1.AddItem CStr(iCounter) & " - " & "Extracting responses - seq number: " & nReqSeqNum
        Form1.List1.ListIndex = iCounter - 1
        Form1.List1.Refresh
        
        RSDirResData.MoveNext
    Else
        GetDirectiveRespData = False
        Exit Function
    End If
    
    Loop
    
End If

'clean up
RSCount.Close
Set RSCount = Nothing
RSDirResData.Close
Set RSDirResData = Nothing
adoConn.Close
Set adoConn = Nothing
Set objStrConn = Nothing
Set adoComm1 = Nothing
Set adoComm2 = Nothing
Form1.Hide
PROC_EXIT:
  Exit Function

PROC_ERROR:
  Err.Raise Err.Number, _
            "clsGetDirResData/GetDirectiveRespData", _
            Err.Description
            
Resume PROC_EXIT


End Function 'GetDirectiveRespData
